<?php
    include_once('Configs/database.php');
    include_once('ThoiKhoaBieu.php');
    include_once('NhomThucHanh.php');
    class LopHocPhan extends database{
        private $malophp;
        private $kihieu;
        private $mahp;
        private $maso;
        private $hocki;
        private $namhoc;
        private $siso;
        private $tuanhoc;
        
        public function setMaLopHP($malophp){
            $this->malophp = $malophp;
        }
        public function getMaLopHP(){
            return $this->malophp;
        }
        
        public function setKiHieu($kihieu){
            $this->kihieu =  $kihieu;
        }
        public function getKiHieu(){
            return $this->kihieu;
        }
        
        public function setMaHP($mahp){
            $this->mahp = $mahp;
        }
        public function getMaHP(){
            return $this->mahp;
        }
        
        public function setMaSo($maso){
            $this->maso = $maso;
        }
        public function getMaSo(){
            return $this->maso;
        }
        
        public function setNamHoc($namhoc){
            $this->namhoc = $namhoc;
        }
        public function getNamHoc(){
            return $this->namhoc;
        } 
        
        public function setHocKi($hocki){
            $this->hocki = $hocki;
        }
        public function getHocKi(){
            return $this->hocki;
        }
        
        public function setSiSo($siso){
            $this->siso = $siso;
        }
        public function getSiSo(){
            return $this->siso;
        }
        
        public function setTuanHoc($tuanhoc){
            $this->tuanhoc = $tuanhoc;
        }
        public function getTuanHoc(){
            return $this->tuanhoc;
        }
                       
                
        public function themLopHP(){
            mysql_query("START TRANSACTION");
            $query = "insert into LopHocPhan(malophp,kihieu,mahp,maso,hocki,namhoc,siso,tuanhoc) values('".$this->getMaLopHP()."',";
            $query .= "'".$this->getKiHieu()."','".$this->getMaHP()."','".$this->getMaSo()."',".$this->getHocKi().",'".$this->getNamHoc()."','".$this->getSiSo()."','".$this->getTuanHoc()."')";
            $this->setQuery($query);
            $i = 0;
            if ($this->executeQuery() >0 ){
                $this->setQuery("insert into NhatKi(malophp) values ('".$this->getMaLopHP()."')");
                
                $i = $this->executeQuery();
                if ($i > 0){
                    mysql_query("COMMIT"); 
                }
                                                                                                                                                                                                                  
            }
            return $i;
            //echo $this->getQuery();
            
        }
        public function suaLopHP(){
            
            $query = "update LopHocPhan set kihieu='".$this->getKiHieu()."', mahp='".$this->getMaHP()."',maso='".$this->getMaSo()."',hocki=".$this->getHocKi().",";
            $query .= "namhoc='".$this->getNamHoc()."',siso=".$this->getSiSo().",tuanhoc='".$this->getTuanHoc()."' where malophp='".$this->getMaLopHP()."'";
            $this->setQuery($query);
            //echo $this->getQuery();
            return $this->executeQuery();
            
            
        }
        public function xoaLopHP(){
            $this->setQuery("delete LopHocPhan where malophp='".$this->getMaLopHP()."'");
            return $this->executeQuery();
        }
        public function thongTinLopHP(){
            $this->setQuery("select * from LopHocPhan where malophp='".$this->getMaLopHP()."'");
           // echo $this->getQuery();
            return $this->fetchAll();
        }
        public function dsLopHPMoTrongHKNH(){
            $this->setQuery("select * from LopHocPhan a, HocPhan b where a.mahp = b.mahp and namhoc='".$this->getNamHoc()."' and hocki=".$this->getHocKi()."");
            return $this->fetchAll();
        }
        public function dsLopHPMoTrongHKNHCuaHP(){
            $this->setQuery("select * from LopHocPhan a, HocPhan b where a.mahp = b.mahp and namhoc='".$this->getNamHoc()."' and hocki=".$this->getHocKi()." and a.mahp='".$this->getMaHP()."'");
            //echo $this->getQuery();
            return $this->fetchAll();
        }
		public function dsLopHPCuaHPMoTrongHKNHCuaCBGD(){
            $this->setQuery("select * from LopHocPhan where maso='".$this->getMaSo()."' and hocki=".$this->getHocKi()." and namhoc='".$this->getNamHoc()."' and mahp='".$this->getMaHP()."'");
			//echo $this->getQuery();
            return $this->fetchAll();
        }
        public function dsLopHPMoTrongHKNHCuaCBGD(){
            $this->setQuery("select * from LopHocPhan where maso='".$this->getMaSo(),"' and hocki=".$this->getHocKi()." and namhoc='".$this->getNamHoc()."'");
            return $this->fetchAll();
        }
        public function dsNhomThucHanh(){
            $this->setQuery("select * from NhomThucHanh where malophp='".$this->getMaLopHP()."' order by manhomth");
            return $this->fetchAll();
            
        }
        public function thoiKhoaBieu(){
            $tkb = new ThoiKhoaBieu();
            $tkb->setMaLopHP($this->getMaLopHP());
            return $tkb->thoiKhoaBieuLopHP();
        }
        public function soTietTHQuyChuanTheoKH(){
            $query = "select a.malophp, round(sum(sotietthuchanh *heso),0) as sotietthquychuan ";
            $query .= "from LopHocPhan a, NhomThucHanh b,LoaiNhomTH c, HocPhan d ";

            $query .= "where a.malophp='".$this->getMaLopHP()."' and a.malophp = b.malophp and b.maloai = c.maloai and b.thoigianapdung = c.thoigianapdung and a.mahp = d.mahp ";
            $query .= "group by a.malophp having count(*)>0";
            $this->setQuery($query);
            $result = $this->fetchAll();
            $rows = mysql_fetch_array($result);
            $sotietthquychuan = 0;
            if ($rows['sotietthquychuan'] > 0) $sotietthquychuan = $rows['sotietthquychuan'];
            
            
            return $sotietthquychuan;
            
        }
         public function soTietTHQuyChuanTT(){
            $nhomth = new NhomThucHanh();
            $query ="select manhomth from NhomThucHanh a where malophp='".$this->getMaLopHP()."'";
            //$query = "select a.malophp, round(sum(sotietthuchanh *heso),0) as sotietthquychuan ";
            //$query .= "from LopHocPhan a, NhomThucHanh b,LoaiNhomTH c, HocPhan d ";

            //$query .= "where a.malophp='".$this->getMaLopHP()."' and a.malophp = b.malophp and b.maloai = c.maloai and b.thoigianapdung = c.thoigianapdung and a.mahp = d.mahp ";
            //$query .= "group by a.malophp having count(*)>0";
            $this->setQuery($query);
            $result = $this->fetchAll();
            
            $tongsotietth  = 0;
            
            while($rows = mysql_fetch_array($result)){
                
                $nhomth->setMaNhomTH($rows['manhomth']);
                $tongsotietth = $tongsotietth + $nhomth->getHeSo() * $nhomth->tongSoTietDaDay();
                
            }
           
            //return $i;
            return $tongsotietth;
            
        }
        public function tongSoTietDaDay(){
            $query = "select malophp,sum(sotiet) as tongsotiet from NhatKiChiTiet where malophp='".$this->getMaLopHP()."' group by malophp";
            $this->setQuery($query);
            $result = $this->fetchAll();
            $rows = mysql_fetch_array($result);
            
            if (trim($rows['tongsotiet']) <= 0)
                $tongsotiet = 0;
            else $tongsotiet = $rows['tongsotiet'];
            
            return $tongsotiet;
        }
        /*public function tongSoTietLTDaDay(){
            $query = "select malophp,sum(sotiet) as tongsotiet from NhatKiChiTiet where malophp='".$this->getMaLopHP()."' group by malophp";
            $this->setQuery($query);
            $result = $this->fetchAll();
            $rows = mysql_fetch_array($result);
            
            if (trim($rows['tongsotiet']) <= 0)
                $tongsotiet = 0;
            else $tongsotiet = $rows['tongsotiet'];
            
            return $tongsotiet;
        }
        public function tongSoTietTHDaDay(){
           return $this->tongSoTietDaDay() - $this->tongSoTietLTDaDay();
        }*/
        public function soTietDayDungKeHoach(){
            $sotietdungkehoach = 0;
            $query = "select a.malophp, tuan, sum(a.sotiet) as sotietdungkhmoituan ";
            $query .= "from NhatKiChiTiet a, NhatKi b, LopHocPhan c, ThoiKhoaBieu d ";
            $query .= "where a.malophp='".$this->getMaLopHP()."' and a.malophp = b.malophp and b.malophp = c.malophp and c.malophp = d.malophp and a.thu = d.thu ";
            $query .= "group by a.malophp, tuan";
             $this->setQuery($query);
             //echo $this->getQuery();
            $result = $this->fetchAll();
            
            while ($rows = mysql_fetch_array($result)){
                if (trim($rows['sotietdungkhmoituan']) <= 0)
                    $sotiet = 0;
                else $sotiet = $rows['sotietdungkhmoituan'];
                $sotietdungkehoach = $sotietdungkehoach + $sotiet;
            }
            return $sotietdungkehoach;
        }
        
    }
?>